#!/usr/bin/env node

/**
 * 查找用户正确的Excel文件
 * 让用户指定要导入的Excel文件
 */

const fs = require('fs');
const path = require('path');
const XLSX = require('xlsx');
const axios = require('axios');
const FormData = require('form-data');

class CorrectExcelFinder {
  constructor() {
    this.baseDir = '/Users/andylau/Dropbox/AppDevelopment/CMB250716';
    this.token = 'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJ1c2VySWQiOjEsImVtYWlsIjoiYWRtaW5AZXhhbXBsZS5jb20iLCJtZW1iZXJzaGlwTGV2ZWwiOiJhZG1pbiIsImlhdCI6MTc1NzE2NDAyMCwiZXhwIjoxNzU3NzY4ODIwLCJpc3MiOiJjdS1tci1idWxsLWFwaSJ9.2pqn01o6rqlOOoOGaEPXNtwIoK-A86kPty3KWnyvY0Q';
  }

  log(message, type = 'info') {
    const timestamp = new Date().toISOString();
    const prefix = {
      'info': '🔍',
      'success': '✅',
      'error': '❌',
      'fix': '🔧',
      'warning': '⚠️'
    }[type] || 'ℹ️';
    console.log(`${prefix} [${timestamp}] ${message}`);
  }

  findAllExcelFiles() {
    this.log('查找所有Excel文件');
    
    const searchPaths = [
      this.baseDir,
      path.join(this.baseDir, 'backend'),
      path.join(this.baseDir, 'backend', 'uploads'),
      path.join(this.baseDir, 'admin'),
      '/Users/andylau/Desktop',
      '/Users/andylau/Downloads'
    ];
    
    const allExcelFiles = [];
    
    searchPaths.forEach(searchPath => {
      if (fs.existsSync(searchPath)) {
        try {
          const files = fs.readdirSync(searchPath);
          files.forEach(file => {
            if (file.endsWith('.xlsx') || file.endsWith('.xls')) {
              const filePath = path.join(searchPath, file);
              const stats = fs.statSync(filePath);
              allExcelFiles.push({
                name: file,
                path: filePath,
                directory: searchPath,
                size: stats.size,
                modified: stats.mtime
              });
            }
          });
        } catch (error) {
          // 忽略无法访问的目录
        }
      }
    });
    
    return allExcelFiles;
  }

  analyzeExcelStructure(filePath) {
    try {
      const workbook = XLSX.readFile(filePath);
      const sheetNames = workbook.SheetNames;
      
      const analysis = {
        fileName: path.basename(filePath),
        filePath: filePath,
        sheets: sheetNames,
        hasRequiredSheets: false,
        sheetDetails: {}
      };
      
      // 检查是否有必需的工作表
      const requiredSheets = ['Dishes', 'Variants', 'Options'];
      analysis.hasRequiredSheets = requiredSheets.every(sheet => sheetNames.includes(sheet));
      
      // 分析每个工作表的内容
      sheetNames.forEach(sheetName => {
        const worksheet = workbook.Sheets[sheetName];
        const data = XLSX.utils.sheet_to_json(worksheet, { header: 1 });
        const nonEmptyRows = data.filter(row => row.some(cell => cell !== undefined && cell !== ''));
        const headers = nonEmptyRows[0] || [];
        const dataRows = nonEmptyRows.slice(1);
        
        analysis.sheetDetails[sheetName] = {
          headers: headers,
          rowCount: dataRows.length,
          sampleData: dataRows.slice(0, 2)
        };
      });
      
      return analysis;
    } catch (error) {
      return {
        fileName: path.basename(filePath),
        filePath: filePath,
        error: error.message
      };
    }
  }

  async testExcelImport(filePath) {
    this.log(`测试Excel文件导入: ${path.basename(filePath)}`);
    
    try {
      const form = new FormData();
      form.append('file', fs.createReadStream(filePath));
      
      const validateResponse = await axios.post('http://localhost:3001/api/admin/menu-import/v2/validate', form, {
        headers: {
          ...form.getHeaders(),
          'Authorization': `Bearer ${this.token}`
        },
        timeout: 60000
      });
      
      const jobId = validateResponse.data.job_id;
      if (!jobId) {
        return { success: false, error: '未获取到任务ID' };
      }
      
      // 等待验证完成
      let attempts = 0;
      let jobStatus = 'validating';
      
      while (jobStatus === 'validating' && attempts < 20) {
        await new Promise(resolve => setTimeout(resolve, 1000));
        
        const statusResponse = await axios.get(`http://localhost:3001/api/admin/menu-import/v2/jobs/${jobId}`, {
          headers: { 'Authorization': `Bearer ${this.token}` }
        });
        
        jobStatus = statusResponse.data.status;
        attempts++;
        
        if (jobStatus === 'validated') {
          // 提交导入
          const commitResponse = await axios.post(`http://localhost:3001/api/admin/menu-import/v2/commit?job_id=${jobId}`, {}, {
            headers: {
              'Authorization': `Bearer ${this.token}`,
              'Content-Type': 'application/json'
            },
            timeout: 60000
          });
          
          if (commitResponse.data.success) {
            return { 
              success: true, 
              jobId: jobId,
              summary: commitResponse.data.summary
            };
          } else {
            return { 
              success: false, 
              error: commitResponse.data.message 
            };
          }
        } else if (jobStatus === 'failed') {
          return { 
            success: false, 
            error: '验证失败',
            details: statusResponse.data
          };
        }
      }
      
      return { success: false, error: '验证超时' };
      
    } catch (error) {
      return { 
        success: false, 
        error: error.message,
        details: error.response?.data
      };
    }
  }

  async checkDatabaseChanges() {
    try {
      const response = await axios.get('http://localhost:3001/api/menu/public/items', {
        headers: { 'Authorization': `Bearer ${this.token}` }
      });
      
      return response.data.data.items;
    } catch (error) {
      return [];
    }
  }

  async run() {
    console.log('\n' + '='.repeat(80));
    console.log('🔍 查找用户正确的Excel文件');
    console.log('🎯 分析所有Excel文件，找出包含Dishes、Variants、Options的文件');
    console.log('='.repeat(80) + '\n');
    
    try {
      // 1. 查找所有Excel文件
      const allFiles = this.findAllExcelFiles();
      
      if (allFiles.length === 0) {
        console.log('❌ 没有找到任何Excel文件');
        return;
      }
      
      this.log(`找到 ${allFiles.length} 个Excel文件`);
      
      // 2. 分析每个Excel文件
      const validFiles = [];
      
      for (let i = 0; i < allFiles.length; i++) {
        const file = allFiles[i];
        this.log(`\n分析文件 ${i + 1}/${allFiles.length}: ${file.name}`);
        this.log(`位置: ${file.directory}`);
        this.log(`大小: ${Math.round(file.size / 1024)}KB`);
        this.log(`修改时间: ${file.modified.toLocaleString()}`);
        
        const analysis = this.analyzeExcelStructure(file.path);
        
        if (analysis.error) {
          this.log(`分析失败: ${analysis.error}`, 'error');
          continue;
        }
        
        this.log(`工作表: ${analysis.sheets.join(', ')}`);
        
        if (analysis.hasRequiredSheets) {
          this.log('✅ 包含必需的工作表 (Dishes, Variants, Options)', 'success');
          
          // 显示每个工作表的详细信息
          ['Dishes', 'Variants', 'Options'].forEach(sheetName => {
            const sheet = analysis.sheetDetails[sheetName];
            if (sheet) {
              this.log(`  ${sheetName}: ${sheet.rowCount} 行数据`);
              this.log(`    表头: ${sheet.headers.slice(0, 5).join(', ')}${sheet.headers.length > 5 ? '...' : ''}`);
            }
          });
          
          validFiles.push({ file, analysis });
        } else {
          this.log('❌ 不包含必需的工作表', 'warning');
        }
      }
      
      if (validFiles.length === 0) {
        console.log('\n' + '='.repeat(80));
        console.log('❌ 没有找到包含Dishes、Variants、Options工作表的Excel文件');
        console.log('💡 请确保您的Excel文件包含以下工作表:');
        console.log('   - Dishes (菜品信息)');
        console.log('   - Variants (规格变体)');
        console.log('   - Options (选项配置)');
        console.log('='.repeat(80) + '\n');
        return;
      }
      
      // 3. 测试有效的Excel文件
      console.log('\n' + '='.repeat(80));
      console.log(`🎯 找到 ${validFiles.length} 个有效的Excel文件，开始测试导入`);
      console.log('='.repeat(80));
      
      for (let i = 0; i < validFiles.length; i++) {
        const { file, analysis } = validFiles[i];
        
        console.log(`\n测试文件 ${i + 1}/${validFiles.length}: ${file.name}`);
        
        const importResult = await this.testExcelImport(file.path);
        
        if (importResult.success) {
          this.log('✅ 导入成功！', 'success');
          this.log(`插入: ${importResult.summary.inserted}, 更新: ${importResult.summary.updated}`);
          
          // 检查数据库变化
          const dbItems = await this.checkDatabaseChanges();
          this.log(`数据库中现有 ${dbItems.length} 条菜品数据`);
          
          console.log('\n' + '='.repeat(80));
          console.log('🎉 Excel导入成功！');
          console.log(`✅ 文件: ${file.name}`);
          console.log(`✅ 位置: ${file.directory}`);
          console.log(`✅ 数据已成功导入到数据库`);
          console.log('💡 请刷新前端页面查看最新数据');
          console.log('='.repeat(80) + '\n');
          
          return;
        } else {
          this.log(`❌ 导入失败: ${importResult.error}`, 'error');
          if (importResult.details) {
            this.log(`详细信息: ${JSON.stringify(importResult.details)}`, 'error');
          }
        }
      }
      
      console.log('\n' + '='.repeat(80));
      console.log('❌ 所有有效的Excel文件都导入失败');
      console.log('💡 请检查上面的错误信息');
      console.log('='.repeat(80) + '\n');
      
    } catch (error) {
      console.log('\n' + '='.repeat(80));
      console.log('❌ 查找Excel文件失败');
      console.log('错误:', error.message);
      console.log('='.repeat(80) + '\n');
    }
  }
}

// 立即运行
if (require.main === module) {
  const finder = new CorrectExcelFinder();
  finder.run().catch(console.error);
}

module.exports = CorrectExcelFinder;